use crate::response::{AppError, AppResult};
use sea_orm::sea_query::{
    DeleteStatement, InsertStatement, PostgresQueryBuilder, SelectStatement, UpdateStatement,
};
use sea_orm::{ConnectionTrait, DatabaseBackend, DatabaseConnection, Statement, TransactionTrait};
use std::sync::Arc;
use tracing::{error, info, instrument};

/// 数据库服务抽象层
#[derive(Clone)]
pub struct DatabaseService {
    db: Arc<DatabaseConnection>,
}

impl DatabaseService {
    /// 创建新的数据库服务实例
    pub fn new(db: DatabaseConnection) -> Self {
        Self { db: Arc::new(db) }
    }

    /// 获取数据库连接引用
    pub fn connection(&self) -> &DatabaseConnection {
        &self.db
    }

    /// 执行查询语句并返回结果
    #[instrument(skip(self, stmt))]
    pub async fn fetch_all<T>(&self, stmt: SelectStatement) -> AppResult<Vec<T>>
    where
        T: sea_orm::FromQueryResult,
    {
        let (sql, values) = stmt.build(PostgresQueryBuilder);
        info!("执行查询SQL: {}", sql);

        T::find_by_statement(Statement::from_sql_and_values(
            DatabaseBackend::Postgres,
            sql,
            values,
        ))
        .all(self.db.as_ref())
        .await
        .map_err(|e| {
            error!("数据库查询失败: {:?}", e);
            AppError::database_with_source("查询操作失败", e)
        })
    }

    /// 执行查询语句并返回单个结果
    #[instrument(skip(self, stmt))]
    pub async fn fetch_one<T>(&self, stmt: SelectStatement) -> AppResult<Option<T>>
    where
        T: sea_orm::FromQueryResult,
    {
        let (sql, values) = stmt.build(PostgresQueryBuilder);
        info!("执行查询SQL: {}", sql);

        T::find_by_statement(Statement::from_sql_and_values(
            DatabaseBackend::Postgres,
            sql,
            values,
        ))
        .one(self.db.as_ref())
        .await
        .map_err(|e| {
            error!("数据库查询失败: {:?}", e);
            AppError::database_with_source("查询操作失败", e)
        })
    }

    /// 执行插入语句
    #[instrument(skip(self, stmt))]
    pub async fn execute_insert(&self, stmt: InsertStatement) -> AppResult<sea_orm::ExecResult> {
        let (sql, values) = stmt.build(PostgresQueryBuilder);
        info!("执行插入SQL: {}", sql);

        self.db
            .execute(Statement::from_sql_and_values(
                DatabaseBackend::Postgres,
                sql,
                values,
            ))
            .await
            .map_err(|e| {
                error!("数据库插入失败: {:?}", e);
                AppError::database_with_source("插入操作失败", e)
            })
    }

    /// 执行更新语句
    #[instrument(skip(self, stmt))]
    pub async fn execute_update(&self, stmt: UpdateStatement) -> AppResult<u64> {
        let (sql, values) = stmt.build(PostgresQueryBuilder);
        info!("执行更新SQL: {}", sql);

        self.db
            .execute(Statement::from_sql_and_values(
                DatabaseBackend::Postgres,
                sql,
                values,
            ))
            .await
            .map(|result| result.rows_affected())
            .map_err(|e| {
                error!("数据库更新失败: {:?}", e);
                AppError::database_with_source("更新操作失败", e)
            })
    }

    /// 执行删除语句
    #[instrument(skip(self, stmt))]
    pub async fn execute_delete(&self, stmt: DeleteStatement) -> AppResult<u64> {
        let (sql, values) = stmt.build(PostgresQueryBuilder);
        info!("执行删除SQL: {}", sql);

        self.db
            .execute(Statement::from_sql_and_values(
                DatabaseBackend::Postgres,
                sql,
                values,
            ))
            .await
            .map(|result| result.rows_affected())
            .map_err(|e| {
                error!("数据库删除失败: {:?}", e);
                AppError::database_with_source("删除操作失败", e)
            })
    }

    /// 开始事务
    #[instrument(skip(self))]
    pub async fn begin_transaction(&self) -> AppResult<sea_orm::DatabaseTransaction> {
        info!("开始数据库事务");

        self.db.begin().await.map_err(|e| {
            error!("开始事务失败: {:?}", e);
            AppError::database_with_source("事务开始失败", e)
        })
    }

    /// 提交事务
    #[instrument(skip(txn))]
    pub async fn commit_transaction(txn: sea_orm::DatabaseTransaction) -> AppResult<()> {
        info!("提交数据库事务");

        txn.commit().await.map_err(|e| {
            error!("提交事务失败: {:?}", e);
            AppError::database_with_source("事务提交失败", e)
        })
    }

    /// 回滚事务
    #[instrument(skip(txn))]
    pub async fn rollback_transaction(txn: sea_orm::DatabaseTransaction) -> AppResult<()> {
        info!("回滚数据库事务");

        txn.rollback().await.map_err(|e| {
            error!("回滚事务失败: {:?}", e);
            AppError::database_with_source("事务回滚失败", e)
        })
    }

    /// 执行原始SQL查询
    #[instrument(skip(self))]
    pub async fn execute_raw(&self, sql: &str) -> AppResult<sea_orm::ExecResult> {
        info!("执行原始SQL: {}", sql);

        self.db.execute_unprepared(sql).await.map_err(|e| {
            error!("原始SQL执行失败: {:?}", e);
            AppError::database_with_source("SQL执行失败", e)
        })
    }

    /// 检查数据库连接
    #[instrument(skip(self))]
    pub async fn ping(&self) -> AppResult<()> {
        self.db.ping().await.map_err(|e| {
            error!("数据库连接检查失败: {:?}", e);
            AppError::database_with_source("数据库连接不可用", e)
        })
    }
}

/// 分页查询构建器
#[derive(Debug, Clone)]
pub struct PaginationBuilder {
    page: u64,
    page_size: u64,
}

impl PaginationBuilder {
    /// 创建分页构建器
    pub fn new(page: Option<u64>, page_size: Option<u64>) -> Self {
        Self {
            page: page.unwrap_or(1).max(1),
            page_size: page_size.unwrap_or(10).min(100).max(1),
        }
    }

    /// 获取偏移量
    pub fn offset(&self) -> u64 {
        (self.page - 1) * self.page_size
    }

    /// 获取限制数量
    pub fn limit(&self) -> u64 {
        self.page_size
    }

    /// 应用分页到查询语句
    pub fn apply_to_query(&self, query: &mut SelectStatement) {
        query.limit(self.page_size).offset(self.offset());
    }

    /// 计算总页数
    pub fn total_pages(&self, total_count: u64) -> u64 {
        (total_count + self.page_size - 1) / self.page_size
    }
}

/// 分页查询结果
#[derive(Debug, Clone, serde::Serialize)]
pub struct PaginatedResult<T> {
    pub data: Vec<T>,
    pub total: u64,
    pub page: u64,
    pub page_size: u64,
    pub total_pages: u64,
}

impl<T> PaginatedResult<T> {
    /// 创建分页结果
    pub fn new(data: Vec<T>, total: u64, pagination: &PaginationBuilder) -> Self {
        Self {
            data,
            total,
            page: pagination.page,
            page_size: pagination.page_size,
            total_pages: pagination.total_pages(total),
        }
    }
}

/// 条件构建器辅助类
pub struct ConditionBuilder;

impl ConditionBuilder {
    /// 添加可选的字符串条件 (LIKE)
    pub fn add_string_like_condition<T>(
        query: &mut SelectStatement,
        column: T,
        value: Option<String>,
    ) where
        T: sea_orm::sea_query::IntoColumnRef,
    {
        if let Some(val) = value {
            if !val.trim().is_empty() {
                query.and_where(sea_orm::sea_query::Expr::col(column).like(format!("%{}%", val)));
            }
        }
    }

    /// 添加可选的相等条件
    pub fn add_eq_condition<T, V>(query: &mut SelectStatement, column: T, value: Option<V>)
    where
        T: sea_orm::sea_query::IntoColumnRef,
        V: Into<sea_orm::sea_query::Value>,
    {
        if let Some(val) = value {
            query.and_where(sea_orm::sea_query::Expr::col(column).eq(val));
        }
    }

    /// 添加可选的IN条件
    pub fn add_in_condition<T, V>(query: &mut SelectStatement, column: T, values: Option<Vec<V>>)
    where
        T: sea_orm::sea_query::IntoColumnRef,
        V: Into<sea_orm::sea_query::Value>,
    {
        if let Some(vals) = values {
            if !vals.is_empty() {
                query.and_where(sea_orm::sea_query::Expr::col(column).is_in(vals));
            }
        }
    }
}

/// 数据库初始化配置
#[derive(Debug, Clone)]
pub struct DatabaseConfig {
    pub url: String,
    pub max_connections: u32,
    pub min_connections: u32,
    pub connect_timeout: std::time::Duration,
    pub idle_timeout: std::time::Duration,
}

impl Default for DatabaseConfig {
    fn default() -> Self {
        Self {
            url: "Postgres://root:password@localhost/test".to_string(),
            max_connections: 100,
            min_connections: 5,
            connect_timeout: std::time::Duration::from_secs(30),
            idle_timeout: std::time::Duration::from_secs(600),
        }
    }
}

/// 初始化数据库连接
#[instrument(skip(config))]
pub async fn init_database(config: DatabaseConfig) -> AppResult<DatabaseService> {
    info!("初始化数据库连接: {}", config.url);

    let db = sea_orm::Database::connect(
        sea_orm::ConnectOptions::new(&config.url)
            .max_connections(config.max_connections)
            .min_connections(config.min_connections)
            .connect_timeout(config.connect_timeout)
            .idle_timeout(config.idle_timeout)
            .to_owned(),
    )
    .await
    .map_err(|e| {
        error!("数据库连接初始化失败: {:?}", e);
        AppError::database_with_source("数据库连接失败", e)
    })?;

    let service = DatabaseService::new(db);

    // 测试连接
    service.ping().await?;

    info!("数据库连接初始化成功");
    Ok(service)
}
